package lib.data;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

import com.google.gdata.data.calendar.CalendarEntry;

import lib.base.EgoSystem;

public class Employee extends Data
{
	private Customer customer;
	private ArrayList<Department> departments;
	
	public Employee(int id, Customer cust) throws Exception
	{
		super("employee");
		addPrimaryKey("customerID", cust.getField("customerID").toString());
		addPrimaryKey("employeeID", Integer.toString(id));
		loadData();
		
		customer = cust;
		departments = new ArrayList<Department>();
		
		_updateRemainingLeavePrevYear();
	}
	
	private void _updateRemainingLeavePrevYear() throws Exception
	{
		String cur_year = String.valueOf( Calendar.getInstance().get(Calendar.YEAR) );
		if (!getField("currentYear").toString().equals( cur_year ))
		{
			// year has changed
			setField("currentYear", cur_year);
			// set remaining leave of previous year
			setField("remainingLeavePrevYear", String.valueOf(
				customer.holidayManager.getRemainingLeaves(this, null)
			));
			
			update();
		}
	}
	
	public void loadDepartments() throws Exception
	{
		departments = new ArrayList<Department>();
		
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT departmentID FROM `department_employee` WHERE " +
			"`customerID`="+customer.getField("customerID")+" AND " +
			"`employeeID`="+getField("employeeID")+";"
		);
		while (rs.next())
		{
			departments.add(customer.getDepartment(rs.getString(1)));
		}
	}
	
	/**
	 * returns the customer Object of this employee
	 * 
	 * @return Customer
	 */
	public Customer getCustomer() throws Exception
	{
		return customer;
	}
	
	/**
	 * gets employee's ID by email
	 * 
	 * @param email String
	 */
	static public int getIdByMail(String email) throws Exception
	{
		// sql query
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
			"SELECT employeeID FROM `employee` "
			+"WHERE account='"+email+"';"
		);
		while (rs.next())
		{
			int id =  rs.getInt("employeeID");
			rs.close();
			stm.close();
			return id;
		}
		
		// unknown employee
		throw new Exception("etApps Error: unknown employee email ("+email+")\n");
	}
	
	/**
	 * Overrides Data.getField()
	 * because holidayCal and workingDays inherits from department
	 */
	public Object getField(String key)
	{
		Object v = super.getField(key);
		
		/*
		 * inherits holidayCal and workingDays from department
		 */
		if (
			(key.equals("holidayCal") || key.equals("workingDays")) &&
			departments.size() > 0
		) {
			if (v.toString().isEmpty() || v.toString().equals("0"))
			{
				String id = this.getField("inheritDepartment").toString();
				for (int i=0; i<departments.size(); i++)
				{
					if (departments.get(i).getField("departmentID").toString().equals( id ))
					{
						return departments.get(i).getField(key);
					}
				}
				// inheritDepartment not found, return first dep
				return departments.get(0).getField(key);
			}
		}
		
		return v;
	}

	/**
	 * Overrides Data.getFieldMap()
	 * because holidayCal and workingDays inherits from department
	 */
	public Map<String, Object> getFieldMap()
	{
		Map<String, Object> new_field = new HashMap<String, Object>();
		
		for (Map.Entry<String, Object> entry : field.entrySet())
		{
			new_field.put(
				entry.getKey(),
				getField(entry.getKey())
			);
		}
		return new_field;
	}
	
	static public Employee newEmployee(Customer cust) throws Exception
	{
		Statement stm = (Statement) EgoSystem.dbConnection.createStatement();
		String custID = cust.getField("customerID").toString();
		// get next employeeID
		String nextID = "0";
		ResultSet rs = stm.executeQuery(
			"SELECT employeeID FROM `employee` WHERE customerID='"+ custID +"' ORDER BY `employeeID` DESC LIMIT 1;"
		);
		if (rs.next())
		{
			nextID = String.valueOf(rs.getInt(1) +1);
		}
		stm.execute(
			"INSERT INTO `employee` ("
			+"`employeeID` ,"
			+"`customerID` ,"
			+"`account` ,"
			+"`holidayCal` ,"
			+"`workingDays` ,"
			+"`entitlementLeave` ,"
			+"`specialLeave` ,"
			+"`isAdmin` ,"
			+"`givenName` ,"
			+"`lastName` ,"
			+ "`remainingLeavePrevYear` ,"
			+ "`firstLogin` ,"
			+ "`currentYear` ,"
			+ "`inheritDepartment` ,"
			+ "`manageOwnLeaves`"
			+") "
			+"VALUES ("
			+"'"+ nextID +"','"+ custID +"','new employee','', '0', '0', '0', '0', '', '', '0', '1', '"
			+ String.valueOf(Calendar.getInstance().get(Calendar.YEAR))
			+"', 0, 0"
			+");"
		);
		Employee e = new Employee(
			Integer.parseInt(nextID),
			cust
		);
		cust.addEmployee(e);
		return e;
	}

	public boolean isUnassigned()
	{
		return departments.size()==0;
	}
	
	public void delete() throws Exception
	{
		// first delete all relations
		Statement stm = EgoSystem.dbConnection.createStatement();
		String sql = "DELETE FROM `department_employee` WHERE "
			+ "customerID="+customer.getField("customerID").toString()
			+ " AND employeeID="+getField("employeeID").toString();
		stm.execute(sql);
		stm.close();
		
		// finally delete my entry
		super.delete();
		
		// delete calendar
		CalendarEntry cal = customer.holidayManager.getCalendar(this);
		if (cal != null)
		{
			cal.delete();
		}
	}
	
	/**
	 * returns the role of this employee in a given department
	 * @param d Department to check
	 * @return [supervisor/member]
	 */
	public String getRole(Department d) throws Exception
	{
		Statement stm = EgoSystem.dbConnection.createStatement();
		ResultSet rs = stm.executeQuery(
				"SELECT role FROM department_employee WHERE "
				+ "customerID="+customer.getField("customerID").toString()+" AND "
				+ "employeeID="+getField("employeeID").toString()+ " AND "
				+ "departmentID="+d.getField("departmentID").toString()+ " LIMIT 1"
		);
		String role = "";
		while (rs.next())
		{
			role = rs.getString("role");
			break;
		}
		
		return role;
	}
	
	public ArrayList<Department> getDepartments()
	{
		return new ArrayList<Department>(departments);
	}

	/**
	 * get departments where I am <role>
	 * @param role String
	 * @return
	 */
	public ArrayList<Department> getDepartments(String role) throws Exception
	{
		ArrayList<Department> deps = getDepartments();
		int i=0;
		while (i<deps.size())
		{
			if (!getRole(deps.get(i)).equals( role ))
			{
				deps.remove(i);
			} else
			{
				i++;
			}
		}
		return deps;
	}
}
